Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing

Imports System.Text
Imports System.Windows.Forms
Imports System.IO
Imports System.Diagnostics
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Public Class clsDatabaseManagent
    Private dbstring As String = String.Empty

    Public Sub GetSQLServer(ByVal cbServers As ComboBox)
        Try
            If MessageBox.Show("Going to look for all SQL Server in the network this might take some time. Do you want to proceed", "SQL Servers", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = DialogResult.Yes Then
                Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers()
                Dim dr As DataRow
                For Each dr In dt.Rows
                    cbServers.Items.Add(dr(0))
                Next
            End If
            Dim sr As StreamReader = New StreamReader("BTErrorManagerDBSchema.sql")
            dbstring = sr.ReadToEnd()
            sr.Close()

        Catch ex As Exception
            Debug.WriteLine(ex.ToString())
            MessageBox.Show(ex.ToString, "Virtual Desk - Setup", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Err.Clear()
        End Try
    End Sub

    Public Sub CreateDB(ByVal srvname As String, ByVal tbDBName As String, ByVal tbBTSAppUser As String)
        Dim sb As StringBuilder = New StringBuilder()

        Try
            sb.AppendLine(String.Format("Starting to create {0} with login to {1}", tbDBName, tbBTSAppUser))
            sb.AppendLine("Connecting to DB Server")
            dbstring = dbstring.Replace("LIGHTWIDGETSVM\BizTalk Application Users", tbBTSAppUser)
            'Me.tbProgress.Text = sb.ToString()
            'Me.tbProgress.ScrollToCaret()
            'Connect to the local, default instance of SQL Server.
            ''  Dim srvname As String =  Me.cbServers.SelectedItem as String 
            Dim srv As Server
            If srvname Is Nothing Then
                srv = New Server()
                sb.AppendLine("Connected to local SQL server")
            Else
                srv = New Server(srvname)
                sb.AppendLine(String.Format("Connected to {0}", srvname))
            End If

            'Me.tbProgress.Text = sb.ToString()
            'Me.tbProgress.ScrollToCaret()
            'Define a Database object variable by supplying the server and the database name arguments in the constructor.
            Dim db As Database = srv.Databases(tbDBName.Trim())
            If Not db Is Nothing Then
                If MessageBox.Show(String.Format("The '{0}' already exists do you want to drop it?", tbDBName), "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
                    db.Drop()
                Else
                    If MessageBox.Show(String.Format("Create the Tables and Stored Procedures for BT Error Manager on  '{0}'?", tbDBName), "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
                        sb.AppendLine("Creating the Tables and Stored Procedures.")
                        '' Me.tbProgress.Text = sb.ToString()
                        db.ExecuteNonQuery(dbstring)
                        sb.AppendLine(String.Format("Created the Tables and Stored Procedures for BT Error Manager on  '{0}'", tbDBName))
                        '' Me.tbProgress.Text = sb.ToString()
                        '' Me.tbProgress.ScrollToCaret()

                    End If
                    sb.AppendLine("Proceed or select another database")
                    'Me.tbProgress.Text = sb.ToString()
                    'Me.tbProgress.ScrollToCaret()
                    Return
                End If
            End If
            sb.AppendLine("Creating the database.....")
            db = New Database(srv, tbDBName)
            '' Me.tbProgress.Text = sb.ToString()
            ''Me.tbProgress.ScrollToCaret()
            'Create the database on the instance of SQL Server.
            db.Create()
            sb.AppendLine("Created the database.")
            sb.AppendLine("Creating the Tables and Stored Procedures.")
            ''  Me.tbProgress.Text = sb.ToString()
            '' Me.tbProgress.ScrollToCaret()
            ''Reference the database and display the date when it was created.
            db.ExecuteNonQuery(dbstring)
            sb.AppendLine("Success!")
            'Me.tbProgress.Text = sb.ToString()
            'Me.tbProgress.ScrollToCaret()

        Catch ex As Exception
            Debug.WriteLine(ex.ToString())
            sb.AppendLine("Failuer:" + ex.Message)
            'Me.tbProgress.Text = sb.ToString()
            'Me.tbProgress.ScrollToCaret()

        End Try

        sb.AppendLine("Proceed or select another database")
        'Me.tbProgress.Text = sb.ToString()
        'Me.tbProgress.ScrollToCaret()

    End Sub
End Class


'----------------------------------------------------------------
' Converted from C# to VB .NET using CSharpToVBConverter(1.2).
' Developed by: Kamal Patel (http://www.KamalPatel.net) 
'----------------------------------------------------------------
